﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TheBullysStore.Controllers;
using TheBullysStore.Models.ModuloServicios.CuotaEdificio;
using System.Data.SqlClient;
using log4net;

namespace TheBullysStore.Models.ModuloServicios.CuotaEdificio
{
    public class CuotaEdificioDAO
    {
        private static ILog log = LogManager.GetLogger(typeof(CuotaEdificioDAO));
        public List<CuotaEdificioBean> ListarCuotaEdificio(CuotaEdificioBean cuotaEdificio)
        {
            SqlConnection sqlCon = null;
            try
            {
                List<CuotaEdificioBean> listacuota = new List<CuotaEdificioBean>();
                
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

                sqlCon = new SqlConnection(cadenaConfiguracion);

                ///////////////////////////////////////////////////////////////////////
                string tipocuotaagua = "";
                int contometro = 0;
                int tipocuotamantenimiento = 0;
                double montoagua = 0;
                double montomantenimiento = 0;
                double montototal = 0;
                int iddepartamento = 0;

                sqlCon.Open();

                string commandString = "select contometro,tipo_prorrateo,tipocuota from edificio where estado=1 and id_edificio="+cuotaEdificio.IdEdificio+ " ";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                while (dataReader.Read())
                {
                    tipocuotaagua = Convert.ToString(dataReader[1]);
                    contometro = Convert.ToInt32(dataReader[0]);
                    tipocuotamantenimiento = Convert.ToInt32(dataReader[2]);

                }
                dataReader.Close();
                sqlCon.Close();

                ///////////////////////////////////////////////////////////////////////

                if ((contometro == 1) && (tipocuotamantenimiento == 1))
                {
                    //CONTOMETRO Y MANTENIMIENTO UNICO
                    double preciounitario = precio_unitario_metrocubico(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes);
                    double diferenciadepartamento = diferencia_por_departamento(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes, preciounitario);
                    
                    sqlCon.Open();
                    //3
                    commandString = "select aaa.id_departamento,aaa.numero_departamento,aaa.monto_cuota,aaa.registro_inicio,aaa.registro_final,aaa.diferencia_agua,aaa.monto_agua,aaa.monto_cuota+aaa.monto_agua "+
                    "from (select d.id_departamento,d.numero as 'numero_departamento',c.monto_cuota as 'monto_cuota',b.registro_inicio,b.registro_final,b.diferencia_agua, ((b.diferencia_agua*(" + preciounitario + "))+(" + diferenciadepartamento + ")) as 'monto_agua' " +                 
                    "from (select * from cuenta_por_cobrar where estado>=1) a inner join (select aa.*,aa.registro_final-aa.registro_inicio as  'diferencia_agua' from consumo_de_agua aa where aa.estado>=1) b  on a.id_cuenta=b.id_cuenta "+
                    "inner join (select z.*,x.montocuota as 'monto_cuota'  from (select * from cuota where estado>=1) z inner join (select * from edificio where estado=1)  x on z.id_edificio=x.id_edificio) c on a.id_cuenta=c.id_cuenta "+
                    "inner join (select * from departamento where estado=1) d on  d.id_departamento=a.id_departamento "+
                    "where a.estado>=1 and c.id_edificio=" + cuotaEdificio.IdEdificio + " and a.anio=" + cuotaEdificio.Anio + " and a.mes=" + cuotaEdificio.Mes + " ) aaa";

                    sqlCmd = new SqlCommand(commandString, sqlCon);

                    dataReader = sqlCmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        CuotaEdificioBean CuotaEdificio = new CuotaEdificioBean();
                        CuotaEdificio.NumeroDepartamento = Convert.ToInt32(dataReader[1]);
                        CuotaEdificio.MontoMantenimiento = Convert.ToDouble(dataReader[2]);
                        CuotaEdificio.AguaInicio = Convert.ToDouble(dataReader[3]);
                        CuotaEdificio.AguaFinal = Convert.ToDouble(dataReader[4]);
                        CuotaEdificio.AguaConsumida = Convert.ToDouble(dataReader[5]);
                        CuotaEdificio.MontoAgua = Convert.ToDouble(dataReader[6]);
                        CuotaEdificio.MontoTotal = Convert.ToDouble(dataReader[7]);
                        CuotaEdificio.TipoCuotaAgua = tipocuotaagua;
                        CuotaEdificio.Contometro = contometro;
                        CuotaEdificio.TipoCuotaMantenimiento = tipocuotamantenimiento;
                        listacuota.Add(CuotaEdificio);
                        montoagua = CuotaEdificio.MontoAgua;
                        montomantenimiento = CuotaEdificio.MontoMantenimiento;
                        montototal = CuotaEdificio.MontoTotal;
                        iddepartamento = Convert.ToInt32(dataReader[0]);
                        updatear_montos(montoagua, montomantenimiento, montototal, iddepartamento, cuotaEdificio.Anio, cuotaEdificio.Mes);

                    }
                    dataReader.Close();
                    
                }

                else if ((contometro == 1) && (tipocuotamantenimiento == 2))
                {
                //CONTOMETRO Y MANTENIMIENTO POR AREA
                    double preciounitario = precio_unitario_metrocubico(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes);
                    double diferenciadepartamento = diferencia_por_departamento(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes, preciounitario);
                    sqlCon.Open();

                    commandString = "select aaa.id_departamento,aaa.numero_departamento,aaa.monto_cuota,aaa.area,aaa.registro_inicio,aaa.registro_final,aaa.diferencia_agua,aaa.monto_agua,aaa.monto_cuota+aaa.monto_agua " +
                    "from (select d.id_departamento,d.numero as 'numero_departamento',d.area*c.monto_cuota as 'monto_cuota',d.area,b.registro_inicio,b.registro_final,b.diferencia_agua, ((b.diferencia_agua*(" + preciounitario+ "))+(" + diferenciadepartamento + ")) as 'monto_agua' " +              
                    "from (select * from cuenta_por_cobrar where estado>=1) a inner join (select aa.*,aa.registro_final-aa.registro_inicio as  'diferencia_agua' from consumo_de_agua aa where aa.estado>=1) b  on a.id_cuenta=b.id_cuenta "+
                    "inner join (select z.*,x.montocuota as 'monto_cuota'  from (select * from cuota where estado>=1) z inner join (select * from edificio where estado=1) x on z.id_edificio=x.id_edificio) c on a.id_cuenta=c.id_cuenta "+
                    "inner join (select * from departamento where estado=1) d on  d.id_departamento=a.id_departamento "+
                    "where a.estado>=1 and c.id_edificio=" + cuotaEdificio.IdEdificio + " and a.anio=" + cuotaEdificio.Anio + " and a.mes=" + cuotaEdificio.Mes + " ) aaa";

                    sqlCmd = new SqlCommand(commandString, sqlCon);

                    dataReader = sqlCmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        CuotaEdificioBean CuotaEdificio = new CuotaEdificioBean();
                        CuotaEdificio.NumeroDepartamento = Convert.ToInt32(dataReader[1]);
                        CuotaEdificio.MontoMantenimiento = Convert.ToDouble(dataReader[2]);
                        CuotaEdificio.AreaDepartamento = Convert.ToDouble(dataReader[3]);
                        CuotaEdificio.AguaInicio = Convert.ToDouble(dataReader[4]);
                        CuotaEdificio.AguaFinal = Convert.ToDouble(dataReader[5]);
                        CuotaEdificio.AguaConsumida = Convert.ToDouble(dataReader[6]);
                        CuotaEdificio.MontoAgua = Convert.ToDouble(dataReader[7]);
                        CuotaEdificio.MontoTotal = Convert.ToDouble(dataReader[8]);
                        CuotaEdificio.TipoCuotaAgua = tipocuotaagua;
                        CuotaEdificio.Contometro = contometro;
                        CuotaEdificio.TipoCuotaMantenimiento = tipocuotamantenimiento;
                        listacuota.Add(CuotaEdificio);
                        montoagua = CuotaEdificio.MontoAgua;
                        montomantenimiento = CuotaEdificio.MontoMantenimiento;
                        montototal = CuotaEdificio.MontoTotal;
                        iddepartamento = Convert.ToInt32(dataReader[0]);
                        updatear_montos(montoagua, montomantenimiento, montototal, iddepartamento, cuotaEdificio.Anio, cuotaEdificio.Mes);
                    }
                    dataReader.Close();
                    
                
                }

                else if ((tipocuotaagua == "Persona") && (tipocuotamantenimiento == 1)) { 
                //AGUA POR PERSONAS Y MANTENIMIENTO UNICO
                    double sumapersona = suma_persona(cuotaEdificio.IdEdificio);
                    double monto = monto_total_factura_agua(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes);

                    if (sumapersona != 0)
                    {
                        sqlCon.Open();

                        commandString = "select aaa.id_departamento,aaa.numero_departamento,aaa.monto_cuota,aaa.numero_personas,aaa.monto_agua,aaa.monto_cuota+aaa.monto_agua " +
                        "from (select d.id_departamento,d.numero as 'numero_departamento',c.monto_cuota as 'monto_cuota',d.num_personas as 'numero_personas',((d.num_personas*1.0*" + monto + ")/" + sumapersona + " ) as 'monto_agua' " +
                        "from (select * from cuenta_por_cobrar where estado>=1) a inner join (select aa.*,aa.registro_final-aa.registro_inicio as 'diferencia_agua' from consumo_de_agua aa where aa.estado>=1) b  on a.id_cuenta=b.id_cuenta " +
                        "inner join (select z.*,x.montocuota as 'monto_cuota'  from (select * from cuota where estado>=1) z inner join (select * from edificio where estado=1) x on z.id_edificio=x.id_edificio) c on a.id_cuenta=c.id_cuenta " +
                        "inner join ( select vv.id_departamento,vv.numero,count(nn.id_persona) as 'num_personas' from  (select * from departamento where estado=1 and id_edificio="+cuotaEdificio.IdEdificio+" )vv " +
                        "left join (select tt.* from persona tt inner join (select * from habitante where es_inquilino=1) hh on hh.id_persona=tt.id_persona where tt.estado=1) nn on nn.id_departamento=vv.id_departamento " +
                        "where vv.estado=1 group by vv.id_departamento, vv.numero  ) d on d.id_departamento=a.id_departamento " +
                        "where a.estado>=1 and c.id_edificio=" + cuotaEdificio.IdEdificio + " and a.anio=" + cuotaEdificio.Anio + " and a.mes=" + cuotaEdificio.Mes + " ) aaa";

                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        dataReader = sqlCmd.ExecuteReader();

                        while (dataReader.Read())
                        {
                            CuotaEdificioBean CuotaEdificio = new CuotaEdificioBean();
                            CuotaEdificio.NumeroDepartamento = Convert.ToInt32(dataReader[1]);
                            CuotaEdificio.MontoMantenimiento = Convert.ToDouble(dataReader[2]);
                            CuotaEdificio.NumPersonas = Convert.ToInt32(dataReader[3]);
                            CuotaEdificio.MontoAgua = Convert.ToDouble(dataReader[4]);
                            CuotaEdificio.MontoTotal = Convert.ToDouble(dataReader[5]);
                            CuotaEdificio.TipoCuotaAgua = tipocuotaagua;
                            CuotaEdificio.Contometro = contometro;
                            CuotaEdificio.TipoCuotaMantenimiento = tipocuotamantenimiento;
                            listacuota.Add(CuotaEdificio);
                            montoagua = CuotaEdificio.MontoAgua;
                            montomantenimiento = CuotaEdificio.MontoMantenimiento;
                            montototal = CuotaEdificio.MontoTotal;
                            iddepartamento = Convert.ToInt32(dataReader[0]);
                            updatear_montos(montoagua, montomantenimiento, montototal, iddepartamento, cuotaEdificio.Anio, cuotaEdificio.Mes);
                        }
                        dataReader.Close();
                        
                    }
                }

                else if ((tipocuotaagua == "Persona") && (tipocuotamantenimiento == 2))
                {
                    //AGUA POR PERSONAS Y MANTENIMIENTO POR AREA
                    double sumapersona = suma_persona(cuotaEdificio.IdEdificio);
                    double monto = monto_total_factura_agua(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes);

                    if (sumapersona != 0)
                    {
                        sqlCon.Open();
                    
                        commandString = "select aaa.id_departamento,aaa.numero_departamento,aaa.area_depa,aaa.monto_cuota,aaa.numero_personas,aaa.monto_agua,aaa.monto_cuota+aaa.monto_agua " +
                        "from (select d.id_departamento,d.numero as 'numero_departamento',c.monto_cuota*d.area as 'monto_cuota',d.area as 'area_depa',d.num_personas as 'numero_personas', ((d.num_personas*1.0*" + monto + ")/" + sumapersona + " ) as 'monto_agua' " +
                        "from (select * from cuenta_por_cobrar where estado>=1) a inner join (select aa.*,aa.registro_final-aa.registro_inicio as 'diferencia_agua' from consumo_de_agua aa where aa.estado>=1) b on a.id_cuenta=b.id_cuenta " +
                        "inner join (select z.*,x.montocuota as 'monto_cuota' from (select * from cuota where estado>=1) z inner join (select * from edificio where estado=1) x on z.id_edificio=x.id_edificio) c on a.id_cuenta=c.id_cuenta " +
                        "inner join ( select vv.id_departamento,vv.numero,vv.area,count(nn.id_persona) as 'num_personas' from  (select * from departamento where estado=1 and id_edificio=" + cuotaEdificio.IdEdificio + " )vv " +
                        "left join (select tt.* from persona tt inner join (select * from habitante where es_inquilino=1) hh on hh.id_persona=tt.id_persona where tt.estado=1) nn on nn.id_departamento=vv.id_departamento " +
                        "where vv.estado=1 group by vv.id_departamento, vv.numero, vv.area  ) d on d.id_departamento=a.id_departamento " +
                        "where a.estado>=1 and c.id_edificio=" + cuotaEdificio.IdEdificio + " and a.anio=" + cuotaEdificio.Anio + " and a.mes=" + cuotaEdificio.Mes + " ) aaa";

                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        dataReader = sqlCmd.ExecuteReader();

                        while (dataReader.Read())
                        {
                            CuotaEdificioBean CuotaEdificio = new CuotaEdificioBean();
                            CuotaEdificio.NumeroDepartamento = Convert.ToInt32(dataReader[1]);
                            CuotaEdificio.MontoMantenimiento = Convert.ToDouble(dataReader[3]);
                            CuotaEdificio.NumPersonas = Convert.ToInt32(dataReader[4]);
                            CuotaEdificio.MontoAgua = Convert.ToDouble(dataReader[5]);
                            CuotaEdificio.AreaDepartamento = Convert.ToDouble(dataReader[2]);
                            CuotaEdificio.MontoTotal = Convert.ToDouble(dataReader[6]);
                            CuotaEdificio.TipoCuotaAgua = tipocuotaagua;
                            CuotaEdificio.Contometro = contometro;
                            CuotaEdificio.TipoCuotaMantenimiento = tipocuotamantenimiento;
                            listacuota.Add(CuotaEdificio);
                            montoagua = CuotaEdificio.MontoAgua;
                            montomantenimiento = CuotaEdificio.MontoMantenimiento;
                            montototal = CuotaEdificio.MontoTotal;
                            iddepartamento = Convert.ToInt32(dataReader[0]);
                            updatear_montos(montoagua, montomantenimiento, montototal, iddepartamento, cuotaEdificio.Anio, cuotaEdificio.Mes);
                        }
                        dataReader.Close();
                        
                    }
                }
                else if ((tipocuotaagua == "Area") && (tipocuotamantenimiento == 1))
                {
                    //AGUA POR AREA Y MANTENIMIENTO UNICO
                    double sumarea = suma_area(cuotaEdificio.IdEdificio);
                    double monto = monto_total_factura_agua(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes);
                    
                    
                    if (sumarea != 0)
                    {

                        sqlCon.Open();

                        commandString = "select aaa.id_departamento,aaa.numero_departamento,aaa.monto_cuota,aaa.area_departamento,aaa.monto_agua,aaa.monto_cuota+aaa.monto_agua " +
                        "from (select d.id_departamento,d.numero as 'numero_departamento',c.monto_cuota as 'monto_cuota',d.area as 'area_departamento',((d.area*" + monto + ")/" +sumarea+ " ) as 'monto_agua' " +
                        "from (select * from cuenta_por_cobrar where estado>=1) a inner join (select aa.*,aa.registro_final-aa.registro_inicio as 'diferencia_agua' from consumo_de_agua aa where aa.estado>=1) b on a.id_cuenta=b.id_cuenta " +
                        "inner join (select z.*,x.montocuota as 'monto_cuota' from (select * from cuota where estado>=1) z inner join (select * from edificio where estado=1) x on z.id_edificio=x.id_edificio) c on a.id_cuenta=c.id_cuenta " +
                        "inner join (select * from departamento where estado=1) d on d.id_departamento=a.id_departamento " +
                        "where a.estado>=1 and c.id_edificio=" + cuotaEdificio.IdEdificio + " and a.anio=" + cuotaEdificio.Anio + " and a.mes=" + cuotaEdificio.Mes + " ) aaa";

                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        dataReader = sqlCmd.ExecuteReader();

                        while (dataReader.Read())
                        {
                            CuotaEdificioBean CuotaEdificio = new CuotaEdificioBean();
                            CuotaEdificio.NumeroDepartamento = Convert.ToInt32(dataReader[1]);
                            CuotaEdificio.MontoMantenimiento = Convert.ToDouble(dataReader[2]);
                            CuotaEdificio.AreaDepartamento = Convert.ToDouble(dataReader[3]);
                            CuotaEdificio.MontoAgua = Convert.ToDouble(dataReader[4]);
                            CuotaEdificio.MontoTotal = Convert.ToDouble(dataReader[5]);
                            CuotaEdificio.TipoCuotaAgua = tipocuotaagua;
                            CuotaEdificio.Contometro = contometro;
                            CuotaEdificio.TipoCuotaMantenimiento = tipocuotamantenimiento;
                            listacuota.Add(CuotaEdificio);
                            montoagua = CuotaEdificio.MontoAgua;
                            montomantenimiento = CuotaEdificio.MontoMantenimiento;
                            montototal = CuotaEdificio.MontoTotal;
                            iddepartamento = Convert.ToInt32(dataReader[0]);
                            updatear_montos(montoagua, montomantenimiento, montototal, iddepartamento, cuotaEdificio.Anio, cuotaEdificio.Mes);
                        }
                        dataReader.Close();
                        
                    }
                }
                else if ((tipocuotaagua == "Area") && (tipocuotamantenimiento == 2))
                {
                    //AGUA POR AREA Y MANTENIMIENTO POR AREA
                    double sumarea = suma_area(cuotaEdificio.IdEdificio);
                    double monto = monto_total_factura_agua(cuotaEdificio.IdEdificio, cuotaEdificio.Anio, cuotaEdificio.Mes);
                    
                    if (sumarea != 0)
                    {
                        sqlCon.Open();

                        commandString = "select aaa.id_departamento,aaa.numero_departamento,aaa.monto_cuota,aaa.area_departamento,aaa.monto_agua,aaa.monto_cuota+aaa.monto_agua " +
                        "from (select d.id_departamento,d.numero as 'numero_departamento',c.monto_cuota*d.area as 'monto_cuota',d.area as 'area_departamento',((d.area*" + monto + ")/" + sumarea + " ) as 'monto_agua' " +
                        "from (select * from cuenta_por_cobrar where estado>=1) a inner join (select aa.*,aa.registro_final-aa.registro_inicio as 'diferencia_agua' from consumo_de_agua aa where aa.estado>=1) b on a.id_cuenta=b.id_cuenta " +
                        "inner join (select z.*,x.montocuota as 'monto_cuota' from (select * from cuota where estado>=1) z inner join (select * from edificio where estado=1) x on z.id_edificio=x.id_edificio) c on a.id_cuenta=c.id_cuenta " +
                        "inner join (select * from departamento where estado=1) d on d.id_departamento=a.id_departamento " +
                        "where a.estado>=1 and c.id_edificio=" + cuotaEdificio.IdEdificio + " and a.anio=" + cuotaEdificio.Anio + " and a.mes=" + cuotaEdificio.Mes + " ) aaa";

                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        dataReader = sqlCmd.ExecuteReader();

                        while (dataReader.Read())
                        {
                            CuotaEdificioBean CuotaEdificio = new CuotaEdificioBean();
                            CuotaEdificio.NumeroDepartamento = Convert.ToInt32(dataReader[1]);
                            CuotaEdificio.MontoMantenimiento = Convert.ToDouble(dataReader[2]);
                            CuotaEdificio.AreaDepartamento = Convert.ToDouble(dataReader[3]);
                            CuotaEdificio.MontoAgua = Convert.ToDouble(dataReader[4]);
                            CuotaEdificio.MontoTotal = Convert.ToDouble(dataReader[5]);
                            CuotaEdificio.TipoCuotaAgua = tipocuotaagua;
                            CuotaEdificio.Contometro = contometro;
                            CuotaEdificio.TipoCuotaMantenimiento = tipocuotamantenimiento;
                            listacuota.Add(CuotaEdificio);
                            montoagua = CuotaEdificio.MontoAgua;
                            montomantenimiento = CuotaEdificio.MontoMantenimiento;
                            montototal = CuotaEdificio.MontoTotal;
                            iddepartamento = Convert.ToInt32(dataReader[0]);
                            updatear_montos(montoagua, montomantenimiento, montototal, iddepartamento, cuotaEdificio.Anio, cuotaEdificio.Mes);
                        }
                        dataReader.Close();
                        
                    }
                }

                //////////////////////////////////////////////////////////////////////////////

                return listacuota;
            }
            catch (Exception ex)
            {
                log.Error("listarEdificio(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }

        public double suma_area(int edificio) {

            double sumarea = 0;

            SqlConnection sqlCon = null;
            try
            {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

            sqlCon = new SqlConnection(cadenaConfiguracion);

            ///////////////////////////////////////////////////////////////////////
            sqlCon.Open();

            string commandString = "select sum(aaa.area) from (select d.id_departamento,d.area as 'area' from (select * from departamento where estado=1) d inner join (select * from cuenta_por_cobrar where estado>=1) c on c.id_departamento=d.id_departamento where d.estado=1 and id_edificio=" + edificio + " group by d.id_departamento,d.area) aaa";
            

            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

            SqlDataReader dataReader = sqlCmd.ExecuteReader();

            while (dataReader.Read())
            {
                if (Convert.IsDBNull(dataReader[0])) { sumarea = 0; } else { sumarea = Convert.ToDouble(dataReader[0]); }

            }
            dataReader.Close();
            return sumarea;
            }
            catch (Exception ex)
            {
                log.Error("listarEdificio(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }

        public int suma_persona(int edificio)
        {

            int sumapersona = 0;
            SqlConnection sqlCon = null;
            try
            {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

            sqlCon = new SqlConnection(cadenaConfiguracion);

            ///////////////////////////////////////////////////////////////////////
            sqlCon.Open();
            //aca si
            string commandString = "select sum(gg.num_personas) from (select count(nn.id_persona) as 'num_personas' from  (select www.id_departamento from departamento www inner join (select * from cuenta_por_cobrar where estado>=1) qqq on www.id_departamento=qqq.id_departamento where www.estado=1 and www.id_edificio=" + edificio + " group by www.id_departamento) vv left join (select tt.* from persona tt inner join (select * from habitante where es_inquilino=1) hh on hh.id_persona=tt.id_persona where tt.estado=1) nn  on nn.id_departamento=vv.id_departamento group by vv.id_departamento) gg";

            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

            SqlDataReader dataReader = sqlCmd.ExecuteReader();

            while (dataReader.Read())
            {
                if (Convert.IsDBNull(dataReader[0])) { sumapersona = 0; } else { sumapersona = Convert.ToInt32(dataReader[0]); }

            }
            dataReader.Close();
            

            return sumapersona;
            }
            catch (Exception ex)
            {
                log.Error("listarEdificio(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }

        public double suma_diferenciaagua(int edificio, int anio, int mes)
        {

            double sumadiferenciaagua = 0;
            SqlConnection sqlCon = null;
            try
            {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

            sqlCon = new SqlConnection(cadenaConfiguracion);

            ///////////////////////////////////////////////////////////////////////
            sqlCon.Open();//aca si

            string commandString = "select sum(aaa.diferencia_agua) from (select aa.registro_final-aa.registro_inicio as 'diferencia_agua' from consumo_de_agua aa where aa.estado>=1 and aa.id_edificio=" + edificio + " and aa.anio=" + anio + " and aa.mes=" + mes + " ) aaa";

            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

            SqlDataReader dataReader = sqlCmd.ExecuteReader();

            while (dataReader.Read())
            {
                if (Convert.IsDBNull(dataReader[0])) { sumadiferenciaagua = 0; } else { sumadiferenciaagua = Convert.ToDouble(dataReader[0]); }

            }
            dataReader.Close();

            return sumadiferenciaagua;
            }
            catch (Exception ex)
            {
                log.Error("listarEdificio(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }

        public double monto_total_factura_agua(int edificio, int anio, int mes)
        {

            double montototalfacturaagua = 0;
            string fecha_inicio = "";
            string fecha_fin = "";
            //////////////////////////////////////////////////////////////////////////////////////////////////////

            if (mes == 11)
            {
                fecha_inicio = "01/12/" + Convert.ToString(anio);
                fecha_fin = "01/01/" + Convert.ToString(anio + 1);
            }
            else if (mes == 12)
            {
                fecha_inicio = "01/01/" + Convert.ToString(anio + 1);
                fecha_fin = "01/02/" + Convert.ToString(anio + 1);
            }
            else if (mes == 10)
            {
                fecha_inicio = "01/11/" + Convert.ToString(anio);
                fecha_fin = "01/12/" + Convert.ToString(anio);
            }
            else if (mes == 9)
            {
                fecha_inicio = "01/10/" + Convert.ToString(anio);
                fecha_fin = "01/11/" + Convert.ToString(anio);
            }
            else if (mes == 8)
            {
                fecha_inicio = "01/09/" + Convert.ToString(anio);
                fecha_fin = "01/10/" + Convert.ToString(anio);

            }
            else
            {


                fecha_inicio = "01/0" + Convert.ToString(mes + 1) + "/" + Convert.ToString(anio);
                fecha_fin = "01/0" + Convert.ToString(mes + 2) + "/" + Convert.ToString(anio);


            }

            ////////////////////////////////////////////////////////////////////////////////////////////////////////
            SqlConnection sqlCon = null;
            try{
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

            sqlCon = new SqlConnection(cadenaConfiguracion);

            ///////////////////////////////////////////////////////////////////////
            sqlCon.Open();


            //fecha_inicio = "01/12/2012";
            //fecha_fin = "01/01/2013";//aca no
            //string commandString = "select aa.monto from documento_de_pago aa inner join (select * from tipo_documento_pago cc where cc.descripcion='Factura') dd on dd.tipo_documento_pago=aa.tipo_documento_pago inner join (select * from tipo_gasto ee where ee.descripcion='Prorateo de Agua') ff on ff.tipo_gasto=aa.tipo_gasto where aa.estado=1 and aa.fecha_registro between '1-12-2012' and '1-1-2013' and aa.id_edificio=" + edificio + " ";
            string commandString = "select aa.monto from documento_de_pago aa inner join (select * from tipo_documento_pago cc where cc.descripcion='Factura') dd on dd.tipo_documento_pago=aa.tipo_documento_pago inner join (select * from tipo_gasto ee where ee.descripcion='Prorateo de Agua') ff on ff.tipo_gasto=aa.tipo_gasto  where aa.estado=1 and aa.fecha_registro between convert(datetime,'" + fecha_inicio + "',103) and convert(datetime,'" + fecha_fin + "',103) and aa.id_edificio=" + edificio + " ";
            
            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

            SqlDataReader dataReader = sqlCmd.ExecuteReader();

            while (dataReader.Read())
            {
                if (Convert.IsDBNull(dataReader[0])) { montototalfacturaagua = 0; } else { montototalfacturaagua = Convert.ToDouble(dataReader[0]); }

            }
            dataReader.Close();
            

            return montototalfacturaagua;
        }
            catch (Exception ex)
            {
                log.Error("listarEdificio(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }

        public int numero_departamentos(int edificio)
        {

            int numdepa = 0;
            SqlConnection sqlCon=null;
            try{
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

            sqlCon = new SqlConnection(cadenaConfiguracion);

            ///////////////////////////////////////////////////////////////////////aqui si
            sqlCon.Open();

            string commandString = "select count(*) from (select d.id_departamento from (select * from departamento where estado=1) d inner join (select * from cuenta_por_cobrar where estado>=1) c on c.id_departamento=d.id_departamento where d.estado=1 and d.id_edificio=" + edificio + " group by d.id_departamento) aaa";

            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

            SqlDataReader dataReader = sqlCmd.ExecuteReader();

            while (dataReader.Read())
            {
                if (Convert.IsDBNull(dataReader[0])) { numdepa = 0; } else { numdepa = Convert.ToInt32(dataReader[0]); }

            }
            dataReader.Close();
            

            return numdepa;
            }
            catch (Exception ex)
            {
                log.Error("listarEdificio(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }
        public double precio_unitario_metrocubico(int edificio, int anio, int mes)
        {

            double precio_metrocubico = 0;
            double volumenconsumido = 0;
            double costovariable = 0;
            string fecha_inicio = "";
            string fecha_fin = "";
            //////////////////////////////////////////////////////////////////////////////////////////////////////

            if (mes == 11)
            {
                fecha_inicio = "01/12/" + Convert.ToString(anio);
                fecha_fin = "01/01/" + Convert.ToString(anio + 1);
            }
            else if (mes == 12)
            {
                fecha_inicio = "01/01/" + Convert.ToString(anio + 1);
                fecha_fin = "01/02/" + Convert.ToString(anio + 1);
            }
            else if (mes == 10)
                {
                    fecha_inicio = "01/11/" + Convert.ToString(anio);
                    fecha_fin = "01/12/" + Convert.ToString(anio);
                }
                else if (mes == 9)
                {
                    fecha_inicio = "01/10/" + Convert.ToString(anio);
                    fecha_fin = "01/11/" + Convert.ToString(anio);
                }
                else if (mes == 8)
                {
                    fecha_inicio = "01/09/" + Convert.ToString(anio);
                    fecha_fin = "01/10/" + Convert.ToString(anio);

                }
                else
                {


                    fecha_inicio = "01/0" + Convert.ToString(mes + 1)+ "/" + Convert.ToString(anio);
                    fecha_fin = "01/0" + Convert.ToString(mes + 2) + "/" + Convert.ToString(anio);


                }
            

            ////////////////////////////////////////////////////////////////////////////////////////////////////////
            SqlConnection sqlCon = null;
            try{
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

            sqlCon = new SqlConnection(cadenaConfiguracion);

            ///////////////////////////////////////////////////////////////////////
            sqlCon.Open();

            //fecha_inicio = "01/12/2012";
            //fecha_fin = "01/01/2013";//aca no
            string commandString = "select aa.volumen_consumido,aa.costo_variable_agua from documento_de_pago aa inner join (select * from tipo_documento_pago cc where cc.descripcion='Factura') dd on dd.tipo_documento_pago=aa.tipo_documento_pago inner join (select * from tipo_gasto ee where ee.descripcion='Prorateo de Agua') ff on ff.tipo_gasto=aa.tipo_gasto  where aa.estado=1 and aa.fecha_registro between convert(datetime,'" + fecha_inicio + "',103) and convert(datetime,'" + fecha_fin + "',103) and aa.id_edificio=" + edificio + " ";
            //string commandString = "select aa.volumen_consumido,aa.costo_variable_agua from documento_de_pago aa inner join (select * from tipo_documento_pago cc where cc.descripcion='Factura') dd on dd.tipo_documento_pago=aa.tipo_documento_pago inner join (select * from tipo_gasto ee where ee.descripcion='Prorateo de Agua') ff on ff.tipo_gasto=aa.tipo_gasto where aa.estado=1 and aa.fecha_registro between '1-12-2012' and '1-1-2013' and aa.id_edificio="+edificio+" ";

            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

            SqlDataReader dataReader = sqlCmd.ExecuteReader();

            while (dataReader.Read())
            {
                if (Convert.IsDBNull(dataReader[0])) { volumenconsumido = 0; } else { volumenconsumido = Convert.ToDouble(dataReader[0]); }
                if (Convert.IsDBNull(dataReader[1])) { costovariable = 0; } else { costovariable = Convert.ToDouble(dataReader[1]); }

            }
            dataReader.Close();
            sqlCon.Close();

            if (volumenconsumido != 0) { precio_metrocubico = (costovariable / volumenconsumido) * (118 / 100); }

            return precio_metrocubico;
        }
            catch (Exception ex)
            {
                log.Error("listarEdificio(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }

        public double diferencia_por_departamento(int edificio, int anio, int mes,double preciounitario) {


            double diferenciadepa = 0;
            if (numero_departamentos(edificio) != 0)
            {
                diferenciadepa = (monto_total_factura_agua(edificio, anio, mes) - (suma_diferenciaagua(edificio, anio, mes) * preciounitario)) / numero_departamentos(edificio);
                
            }

            return diferenciadepa;
        
        
        }

        public string updatear_montos(double montoagua,double montomantenimiento,double montototal,int departamento,int anio ,int mes) 

            
            {

                string monto = "";
                SqlConnection sqlCon = null;
                try{
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

                sqlCon = new SqlConnection(cadenaConfiguracion);

                ///////////////////////////////////////////////////////////////////////
                sqlCon.Open();

                string commandString = "update consumo_de_agua set costo="+redondear_arriba(montoagua)+", estado=2 where id_departamento="+departamento+" and anio="+anio+" and mes="+mes+" and estado>=1";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                sqlCmd.ExecuteNonQuery();

                commandString = "update cuota set monto=" + redondear_arriba(montomantenimiento) + ", estado=2 where id_departamento=" + departamento + " and anio=" + anio + " and mes=" + mes + " and estado>=1";

                sqlCmd = new SqlCommand(commandString, sqlCon);

                sqlCmd.ExecuteNonQuery();


                commandString = "update cuenta_por_cobrar set monto_mensual=" + redondear_arriba(montototal) + ", estado=2 where id_departamento=" + departamento + " and anio=" + anio + " and mes=" + mes + " and estado>=1";

                sqlCmd = new SqlCommand(commandString, sqlCon);

                sqlCmd.ExecuteNonQuery();
            
            
                sqlCon.Close();

                return monto;
                }
                catch (Exception ex)
                {
                    log.Error("listarEdificio(EXCEPTION): ", ex);
                    throw ex;
                }
                finally
                {
                    sqlCon.Close();
                }
            }

        public double redondear_arriba(double a) {

            double b = 0;
            if (System.Math.Round(a, 1) < a) { b = System.Math.Round(a, 1) + 0.1; } else { b = System.Math.Round(a, 1); }
            return b;
        
        
        }

    }
}